package cn.interestingshop.dao.impl;

import cn.interestingshop.dao.BaseDao;
import cn.interestingshop.dao.UserDao;
import cn.interestingshop.entity.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl extends BaseDao implements UserDao {
    @Override
    public User selectOne(String account, String password) {
        User l = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "SELECT * FROM `interesting_shop_db`.`t_user` WHERE `account` = ? AND `password` = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,account);
            ps.setString(2,password);
            rs = ps.executeQuery();
            while (rs.next()) {
                l = new User();

                l.setId(rs.getInt("id"));
                l.setAccount(rs.getString("account"));
                l.setNickName(rs.getString("nick_name"));
                l.setPassword(rs.getString("password"));
                l.setGender(rs.getInt("gender"));
                l.setIdCardNo(rs.getString("id_card_no"));
                l.setEmail(rs.getString("email"));
                l.setPhone(rs.getString("phone"));
                l.setType(rs.getInt("type"));
                l.setOpenId(rs.getString("open_id"));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(null,ps,conn);
        }
        return l;
    }

    @Override
    public int insert(User user) {
        String sql="INSERT INTO `interesting_shop_db`.`t_user`" +
                "(`account`, `nick_name`, `password`, `gender`,`id_card_no`, `email`, `phone`) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?)";
        return executeUpdate(sql,new Object[]{
                user.getAccount(),
                user.getNickName(),
                user.getPassword(),
                user.getGender(),
                user.getIdCardNo(),
                user.getEmail(),
                user.getPhone()
        });
    }

    @Override
    public List<User> selectAll(int pageNo, int pageSize) {
        List<User> list=new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "SELECT * FROM `interesting_shop_db`.`t_user` ORDER BY `id` DESC LIMIT ?, ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,(pageNo-1)*pageSize);
            ps.setInt(2,pageSize);
            rs = ps.executeQuery();
            while (rs.next()) {
                User l = new User();

                l.setId(rs.getInt("id"));
                l.setAccount(rs.getString("account"));
                l.setNickName(rs.getString("nick_name"));
                l.setPassword(rs.getString("password"));
                l.setGender(rs.getInt("gender"));
                l.setIdCardNo(rs.getString("id_card_no"));
                l.setEmail(rs.getString("email"));
                l.setPhone(rs.getString("phone"));
                l.setType(rs.getInt("type"));
                l.setOpenId(rs.getString("open_id"));

                list.add(l);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(rs,ps,conn);
        }
        return list;
    }

    @Override
    public int selectCount() {
        int count=0;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "SELECT count(1) FROM `interesting_shop_db`.`t_user`";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                count=rs.getInt(1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(null,ps,conn);
        }
        return count;
    }

    @Override
    public int delete(int id) {
        String sql="DELETE FROM `interesting_shop_db`.`t_user` WHERE `id` = ?";
        return executeUpdate(sql,new Object[]{
                id
        });
    }

    @Override
    public User selectOne(int id) {
        User l=null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            String sql = "SELECT * FROM `interesting_shop_db`.`t_user` WHERE `id` = ? ";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while (rs.next()) {
                l = new User();

                l.setId(rs.getInt("id"));
                l.setAccount(rs.getString("account"));
                l.setNickName(rs.getString("nick_name"));
                l.setPassword(rs.getString("password"));
                l.setGender(rs.getInt("gender"));
                l.setIdCardNo(rs.getString("id_card_no"));
                l.setEmail(rs.getString("email"));
                l.setPhone(rs.getString("phone"));
                l.setType(rs.getInt("type"));
                l.setOpenId(rs.getString("open_id"));
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll(rs,ps,conn);
        }
        return l;
    }

    @Override
    public int update(User user) {
        String sql="UPDATE `interesting_shop_db`.`t_user` " +
                "SET `account` = ?, `nick_name` =?, `password` =?, " +
                "`gender` = ?, `id_card_no` = ?, `email` = ?, `phone` =?, " +
                "`type` = ? WHERE `id` = ?";
        return executeUpdate(sql,new Object[]{
                user.getAccount(),
                user.getNickName(),
                user.getPassword(),
                user.getGender(),
                user.getIdCardNo(),
                user.getEmail(),
                user.getPhone(),
                user.getType(),
                user.getId()
        });
    }
}
